package com.util.fileUpload;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.entity.other.City;
import com.util.dao.BasicDao;


public class ExcelTest {
	
	//记录文件路径
	public static String filePath = "D:\\city.xls";
	
	public static void main(String[] args) throws Exception
	{
		BasicDao basicDao;
		City city = new City();
		ApplicationContext cxt = new ClassPathXmlApplicationContext("/applicationContext-core.xml");
		basicDao = (BasicDao)cxt.getBean("basicDao");  
		
		// 创建对Excel工作簿文件的引用
        try {
			HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath));
			
			// 在Excel文档中，第一张工作表的缺省索引是0
			// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0)
			HSSFSheet sheet = workbook.getSheet("Sheet1");
			
		   //获取sheet中的所有行数
			int rows = sheet.getPhysicalNumberOfRows();
			
			for(int i=0; i<rows; i++)
			{
				HSSFRow row = sheet.getRow(i);
				if(row != null)
				{
					int cells = row.getPhysicalNumberOfCells();
					String value = "";
					//遍历列
					for(int j=0; j<cells; j++)
					{
						HSSFCell cell = row.getCell(j);
						if(cell != null)
						{
							switch(cell.getCellType())
							{
								case HSSFCell.CELL_TYPE_FORMULA:
									break;
								case HSSFCell.CELL_TYPE_NUMERIC:
									Double double1 = cell.getNumericCellValue();
									value += double1.intValue() + ",";
									break;
								case HSSFCell.CELL_TYPE_STRING:
									value += cell.getStringCellValue() + ",";
									break;
							    default:
							    	value += "0";
							    	break;
							}
						}
					}
					String [] val = value.split(",");
					city.setCityId(Integer.valueOf(val[0]));
					city.setCityName(val[1]);
					city.setProvinceId(Integer.valueOf(val[2]));
					basicDao.insert(city);
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
